import { BusinessException } from '../../types/exceptions.js';
import { SysDisable } from '../../enums/system.js';
import { Pool as MySQLPool } from 'mysql2/promise';
import { Pool as PgPool } from 'pg';
import {
  DatabaseOperations,
  DatabaseOperationsFactory,
  BaseMySQLOperations,
  BasePostgreSQLOperations,
} from '../../utils/database-operations.js';
import {
  SysUser,
  CreateSysUserDto,
  UpdateSysUserDto,
  SysUserQueryDto,
} from '../../entities/system/SysUser.js';

// SysUser特定的数据库操作接口
interface SysUserOperations extends DatabaseOperations<SysUser> {
  loadUser(userName: string): Promise<SysUser | null>;
  loadUserByMobile(mobile: string): Promise<SysUser | null>;
  loadUserByOpenId(openId: string): Promise<SysUser | null>;
}

// MySQL数据库操作实现
class MySQLOperations extends BaseMySQLOperations<SysUser> implements SysUserOperations {
  constructor(pool: MySQLPool) {
    super(pool);
  }

  async create(user: CreateSysUserDto): Promise<number> {
    const now = Date.now();
    const sql = `INSERT INTO sys_user (
                      user_name, nick_name, open_id, password, mobile, 
                      email, avatar, sex, dept_id, remark, status,
                      time, update_time
                  ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`;
    const params: (string | number)[] = [
      user.user_name as string,
      user.nick_name as string,
      user.open_id as string,
      user.password as string,
      user.mobile as string,
      user.email as string,
      user.avatar as string,
      user.sex as number,
      user.dept_id as number,
      user.remark as string,
      user.status as number,
      now,
      now,
    ];
    return this.executeInsert(sql, params);
  }

  async update(id: number, user: UpdateSysUserDto): Promise<boolean> {
    const updateFields: string[] = [];
    const values: (string | number)[] = [];

    Object.entries(user).forEach(([key, value]) => {
      if (value !== undefined && value !== null && key !== 'id') {
        updateFields.push(`${key} = ?`);
        values.push(value as string | number);
      }
    });

    if (updateFields.length === 0) {
      throw new BusinessException('没有要更新的字段');
    }

    updateFields.push('update_time = ?');
    values.push(Date.now());
    values.push(id);

    const sql = `UPDATE sys_user SET ${updateFields.join(', ')} WHERE id = ? AND disabled = ${SysDisable.ENABLE}`;
    return (await this.executeUpdate(sql, values)) > 0;
  }

  async delete(id: number): Promise<boolean> {
    const sql = `UPDATE sys_user SET disabled = ${SysDisable.DISABLE}, update_time = ? WHERE id = ?`;
    return (await this.executeUpdate(sql, [Date.now(), id])) > 0;
  }

  async loadUser(user_name: string): Promise<SysUser | null> {
    const sql = 'SELECT * FROM sys_user WHERE user_name = ? AND disabled = ?';
    return this.queryOne(sql, [user_name, SysDisable.ENABLE]);
  }

  async loadUserByMobile(mobile: string): Promise<SysUser | null> {
    const sql = 'SELECT * FROM sys_user WHERE mobile = ? AND disabled = ?';
    return this.queryOne(sql, [mobile, SysDisable.ENABLE]);
  }

  async loadUserByOpenId(openId: string): Promise<SysUser | null> {
    const sql = 'SELECT * FROM sys_user WHERE open_id = ? AND disabled = ?';
    return this.queryOne(sql, [openId, SysDisable.ENABLE]);
  }

  async getById(id: number): Promise<SysUser | null> {
    const sql = 'SELECT * FROM sys_user WHERE id = ? AND disabled = ?';
    return this.queryOne(sql, [id, SysDisable.ENABLE]);
  }

  async list(
    page: number = 1,
    pageSize: number = 10,
    conditions: SysUserQueryDto = {}
  ): Promise<{
    list: SysUser[];
    total: number;
    page: number;
    pageSize: number;
  }> {
    const whereConditions: string[] = [`disabled = ${SysDisable.ENABLE}`];
    const params: (string | number)[] = [];

    // 动态构建查询条件
    if (conditions.user_name) {
      whereConditions.push('user_name LIKE ?');
      params.push(`%${conditions.user_name}%`);
    }
    if (conditions.nick_name) {
      whereConditions.push('nick_name LIKE ?');
      params.push(`%${conditions.nick_name}%`);
    }
    if (conditions.mobile) {
      whereConditions.push('mobile LIKE ?');
      params.push(`%${conditions.mobile}%`);
    }
    if (conditions.status !== undefined) {
      whereConditions.push('status = ?');
      params.push(conditions.status);
    }

    const whereClause = whereConditions.join(' AND ');

    // 查询总数
    const countResult = await this.queryOne<{ total: number }>(
      `SELECT COUNT(*) as total FROM sys_user WHERE ${whereClause}`,
      params
    );
    const total = countResult?.total || 0;

    // 查询列表
    const offset = (page - 1) * pageSize;
    const list = await this.queryMany(
      `SELECT * FROM sys_user WHERE ${whereClause} ORDER BY time DESC LIMIT ? OFFSET ?`,
      [...params, pageSize, offset]
    );

    return {
      list,
      total,
      page,
      pageSize,
    };
  }
}

// PostgreSQL数据库操作实现
class PostgreSQLOperations extends BasePostgreSQLOperations<SysUser> implements SysUserOperations {
  constructor(pool: PgPool) {
    super(pool);
  }

  async create(user: CreateSysUserDto): Promise<number> {
    const now = Date.now();
    const sql = `INSERT INTO sys_user (
                   user_name, nick_name, open_id, password, mobile, 
                   email, avatar, sex, dept_id, remark, status,
                   time, update_time
                 ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13)
                 RETURNING id`;
    const params: (string | number)[] = [
      user.user_name as string,
      user.nick_name as string,
      user.open_id as string,
      user.password as string,
      user.mobile as string,
      user.email as string,
      user.avatar as string,
      user.sex as number,
      user.dept_id as number,
      user.remark as string,
      user.status as number,
      now,
      now,
    ];
    return this.executeInsert(sql, params);
  }

  async update(id: number, user: UpdateSysUserDto): Promise<boolean> {
    const updateFields: string[] = [];
    const values: (string | number)[] = [];
    let paramIndex = 1;

    Object.entries(user).forEach(([key, value]) => {
      if (value !== undefined && key !== 'id') {
        updateFields.push(`${key} = $${paramIndex++}`);
        values.push(value as string | number);
      }
    });

    if (updateFields.length === 0) {
      throw new BusinessException('没有要更新的字段');
    }

    updateFields.push(`update_time = $${paramIndex++}`);
    values.push(Date.now());
    values.push(id);

    const sql = `UPDATE sys_user SET ${updateFields.join(', ')} WHERE id = $${paramIndex} AND disabled = ${SysDisable.ENABLE}`;
    return (await this.executeUpdate(sql, values)) > 0;
  }

  async delete(id: number): Promise<boolean> {
    const sql = `UPDATE sys_user SET disabled = ${SysDisable.DISABLE}, update_time = $1 WHERE id = $2`;
    return (await this.executeUpdate(sql, [Date.now(), id])) > 0;
  }

  async loadUser(user_name: string): Promise<SysUser | null> {
    const sql = 'SELECT * FROM sys_user WHERE user_name = $1 AND disabled = $2';
    return this.queryOne(sql, [user_name, SysDisable.ENABLE]);
  }

  async loadUserByMobile(mobile: string): Promise<SysUser | null> {
    const sql = 'SELECT * FROM sys_user WHERE mobile = $1 AND disabled = $2';
    return this.queryOne(sql, [mobile, SysDisable.ENABLE]);
  }

  async loadUserByOpenId(openId: string): Promise<SysUser | null> {
    const sql = 'SELECT * FROM sys_user WHERE open_id = $1 AND disabled = $2';
    return this.queryOne(sql, [openId, SysDisable.ENABLE]);
  }

  async getById(id: number): Promise<SysUser | null> {
    const sql = 'SELECT * FROM sys_user WHERE id = $1 AND disabled = $2';
    return this.queryOne(sql, [id, SysDisable.ENABLE]);
  }

  async list(
    page: number = 1,
    pageSize: number = 10,
    conditions: SysUserQueryDto = {}
  ): Promise<{
    list: SysUser[];
    total: number;
    page: number;
    pageSize: number;
  }> {
    const whereConditions: string[] = [`disabled = ${SysDisable.ENABLE}`];
    const params: (string | number)[] = [];
    let paramIndex = 1;

    // 动态构建查询条件
    if (conditions.user_name) {
      whereConditions.push(`user_name LIKE $${paramIndex++}`);
      params.push(`%${conditions.user_name}%`);
    }
    if (conditions.nick_name) {
      whereConditions.push(`nick_name LIKE $${paramIndex++}`);
      params.push(`%${conditions.nick_name}%`);
    }
    if (conditions.mobile) {
      whereConditions.push(`mobile LIKE $${paramIndex++}`);
      params.push(`%${conditions.mobile}%`);
    }
    if (conditions.status !== undefined) {
      whereConditions.push(`status = $${paramIndex++}`);
      params.push(conditions.status);
    }

    const whereClause = whereConditions.join(' AND ');

    // 查询总数
    const countResult = await this.queryOne<{ total: number }>(
      `SELECT COUNT(*) as total FROM sys_user WHERE ${whereClause}`,
      params
    );
    const total = countResult?.total || 0;

    // 查询列表
    const offset = (page - 1) * pageSize;
    const list = await this.queryMany(
      `SELECT * FROM sys_user WHERE ${whereClause} ORDER BY time DESC LIMIT $${paramIndex++} OFFSET $${paramIndex}`,
      [...params, pageSize, offset]
    );

    return {
      list,
      total,
      page,
      pageSize,
    };
  }
}

export class SysUserService {
  private dbOperations: SysUserOperations | null = null;

  constructor() {
    // 延迟初始化，避免在应用启动时调用context方法
  }

  private getDbOperations(): SysUserOperations {
    if (!this.dbOperations) {
      this.dbOperations = DatabaseOperationsFactory.create<SysUserOperations>(
        MySQLOperations,
        PostgreSQLOperations
      );
    }
    return this.dbOperations;
  }

  /**
   * 创建用户
   * @param user 用户信息
   * @returns 创建的用户ID
   */
  async create(user: CreateSysUserDto): Promise<number> {
    return this.getDbOperations().create(user);
  }

  /**
   * 更新用户信息
   */
  async update(id: number, user: UpdateSysUserDto): Promise<boolean> {
    return this.getDbOperations().update(id, user);
  }

  // 删除用户（软删除）
  async delete(id: number): Promise<boolean> {
    return this.getDbOperations().delete(id);
  }

  /**
   * 根据用户名加载用户
   * @param user_name 用户名
   * @returns
   */
  async loadUser(user_name: string): Promise<SysUser | null> {
    return this.getDbOperations().loadUser(user_name);
  }

  /**
   * 根据手机号加载用户
   * @param mobile 手机号
   * @returns
   */
  async loadUserByMobile(mobile: string): Promise<SysUser | null> {
    return this.getDbOperations().loadUserByMobile(mobile);
  }

  /**
   * 根据OpenID加载用户
   * @param openId 微信OpenID
   * @returns
   */
  async loadUserByOpenId(openId: string): Promise<SysUser | null> {
    return this.getDbOperations().loadUserByOpenId(openId);
  }

  /**
   * 根据ID获取用户详情
   * @param id 用户ID
   * @returns
   */
  async getById(id: number): Promise<SysUser | null> {
    return this.getDbOperations().getById(id);
  }

  // 获取用户列表
  async list(
    page: number = 1,
    pageSize: number = 10,
    query: SysUserQueryDto = {}
  ): Promise<{ total: number; list: SysUser[]; page: number; pageSize: number }> {
    return this.getDbOperations().list(page, pageSize, query);
  }
}
